package com.api.shopapi.mapper;

import com.api.shopapi.entity.Recharge;
import com.api.shopapi.entity.dto.RechargeDTO;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

@Mapper
@Repository
public interface RechargeMapper {



    @Insert("insert into  recharge " +
            "(" +
            "RechargeCode, " +
            "ElderlyCode, " +
            "PayMoney, " +
            "AddBalance, " +
            "CreateTime, " +
            "StaffCode, " +
            "GovernmentCode, " +
            "Type," +
            "RechargeGroupCode" +
            ") " +
            "VALUES " +
            "(" +
            "#{rechargeDTO.RechargeCode} ," +
            "#{rechargeDTO.ElderlyCode} ," +
            "#{rechargeDTO.PayMoney} ," +
            "#{rechargeDTO.AddBalance} ," +
            "#{rechargeDTO.CreateTime} ," +
            "#{rechargeDTO.StaffCode} ," +
            "#{rechargeDTO.GovernmentCode} ," +
            "#{rechargeDTO.Type}, " +
            "#{rechargeDTO.RechargeGroupCode} " +
            ")")
    int insert(@Param("rechargeDTO") RechargeDTO rechargeDTO);

//不根据时间筛选查询充值记录
    @Select("<script>" +
            "select  * from  recharge r left join elderly e on r.ElderlyCode=e.ElderlyCode " +
            "left join user u on u.UserCode=e.UserCode " +
            "where r.GovernmentCode=#{governmentCode} " +
            "<if test ='name !=null and name!=\"\" '>" +
            " and u.Name like concat('%',#{name},'%') " +
            "</if>" +
            "<if test ='phone !=null and  phone!=\"\" '>" +
            " and u.Phone =#{phone} " +
            "</if> " +
            "order by r.CreateTime desc " +
            "</script>")
    List<RechargeDTO> getPageList(@Param("name") String name, @Param("phone") String phone,@Param("governmentCode") String governmentCode);

//根据时间筛选查询充值记录
   @Select("<script>" +
            "select  * from  recharge r left join elderly e on r.ElderlyCode=e.ElderlyCode " +
            "left join user u on u.UserCode=e.UserCode " +
            "where r.GovernmentCode=#{governmentCode} " +
            "<if test ='name !=null and name!=\"\" '>" +
            " and u.Name like concat('%',#{name},'%') " +
            "</if>" +
            "<if test ='phone !=null and  phone!=\"\" '>" +
            " and u.Phone =#{phone} " +
            "</if> " +
            "and r.CreateTime BETWEEN #{startTime} and #{endTime} " +
            "order by r.CreateTime desc " +
            "</script>")
    List<RechargeDTO> getPageList1(@Param("name") String name, @Param("phone") String phone,@Param("governmentCode") String governmentCode,@Param("startTime") String startTime,@Param("endTime") String endTime);


    @Select("select * from recharge where RechargeGroupCode=#{rechargeGroupCode}")
    List<RechargeDTO> getForInfo(@Param("rechargeGroupCode") String rechargeGroupCode);


//根据时间筛选查询付款金额的和
    @Select("<script>" +
            "select  COALESCE(sum(r.PayMoney),0) from  recharge r left join elderly e on r.ElderlyCode=e.ElderlyCode " +
            "left join user u on u.UserCode=e.UserCode " +
            "where r.GovernmentCode=#{governmentCode} " +
            "<if test ='name !=null and name!=\"\" '>" +
            " and u.Name like concat('%',#{name},'%') " +
            "</if>" +
            "<if test ='phone !=null and  phone!=\"\" '>" +
            " and u.Phone =#{phone} " +
            "</if> " +
            "and r.CreateTime BETWEEN #{startTime} and #{endTime} " +
            "order by r.CreateTime desc " +
            "</script>")
    double getTotalPayMoney(@Param("name") String name, @Param("phone") String phone,@Param("governmentCode") String governmentCode,@Param("startTime") String startTime,@Param("endTime") String endTime);

//    根据时间筛选查询所有充值记录扥和
    @Select("<script>" +
            "select  COALESCE(sum(r.AddBalance),0) from  recharge r left join elderly e on r.ElderlyCode=e.ElderlyCode " +
            "left join user u on u.UserCode=e.UserCode " +
            "where r.GovernmentCode=#{governmentCode} " +
            "<if test ='name !=null and name!=\"\" '>" +
            " and u.Name like concat('%',#{name},'%') " +
            "</if>" +
            "<if test ='phone !=null and  phone!=\"\" '>" +
            " and u.Phone =#{phone} " +
            "</if> " +
            "and r.CreateTime BETWEEN #{startTime} and #{endTime} " +
            "order by r.CreateTime desc " +
            "</script>")
    double getTotalAddBalance(@Param("name") String name, @Param("phone") String phone,@Param("governmentCode") String governmentCode,@Param("startTime") String startTime,@Param("endTime") String endTime);

    @Select("select * from recharge where ElderlyCode=#{elderlyCode} and (CreateTime between #{startTime} and #{endTime}) ")
    List<Recharge> getAddBalance(@Param("elderlyCode") String elderlyCode,
                                 @Param("startTime") Date startTime,
                                 @Param("endTime") Date endTime);
}
